# **********************PDF文件的转换*******************************

import os
import docx
import pandas as pd
from configparser import ConfigParser
from io import StringIO
from io import open
from pdfminer.pdfinterp import PDFResourceManager
from pdfminer.pdfinterp import process_pdf
from pdfminer.converter import TextConverter
from pdfminer.layout import LAParams
from docx import Document

handle_all = input('进行全部操作？是请输入(y),否则输入（n）:').upper()

if handle_all == 'Y':
    pdf_to_excel ='Y'
    excel_as_one ='Y'
    excel_for_match ='Y'
    modul_audit_count ='Y'

else:
    pdf_to_excel= input('是否进行PDF转Excel？是请输入(y),否则输入（n）:').upper()
    excel_as_one = input('是否进行Excel合并？是请输入(y)，否则输入（n）:').upper()
    excel_for_match = input('是否匹配类筛选？是请输入(y)，否则输入（n）:').upper()
    modul_audit_count = input('是否计算平均扣分状态？是请输入(y)，否则输入（n）:').upper()



#定义工作路径
dirs = 'D:\\python\\docx_word_handle'


def read_from_pdf(file_path):
    with open(file_path, 'rb') as file:
        resource_manager = PDFResourceManager()
        return_str = StringIO()
        lap_params = LAParams()

        device = TextConverter(
            resource_manager, return_str, laparams=lap_params)
        process_pdf(resource_manager, device, file)
        device.close()
        content = return_str.getvalue()
        return_str.close()
        return content

def save_text_to_word(content, file_path):
    doc = Document()
    for line in content.split('\n'):
        paragraph = doc.add_paragraph()
        paragraph.add_run(remove_control_characters(line))
    doc.save(file_path)

def remove_control_characters(content):
    mpa = dict.fromkeys(range(32))
    return content.translate(mpa)

def pdf_to_word(pdf_file_path, word_file_path):
    content = read_from_pdf(pdf_file_path)
    save_text_to_word(content, word_file_path)




#******************************PDF转Word****************************************
if pdf_to_excel == "Y":
    print('批量PDF转Word开始')
    files = os.listdir(dirs)  # 获取文件名列表
    for item in files:
        if os.path.isfile(os.path.join(dirs, item)):
            if item.find('.PDF') != -1 or item.find('.pdf') != -1:
                pdf_full_path = os.path.join(dirs, item)
                word_full_path = os.path.join(dirs, item[:-4] + '.docx')
                pdf_to_word(pdf_full_path, word_full_path)
    print('批量PDF转Word完成！')

# *****************************Word转换换为Excel*************************************
 #自定义函数，实现word行删除造作
def delete_paragraph(paragraph):
    p = paragraph._element
    p.getparent().remove(p)
    p._p = p._element = None

 #自定义函数：实现word内容清理
def docx_to_excel(doc_path,excel_path,file_name):

    doc = docx.Document(doc_path)
    # 删除空白行
    for paragraph in doc.paragraphs:
        if paragraph.text == '':
            delete_paragraph(paragraph)

    # 删除开头内容
    for paragraph in doc.paragraphs:
        if paragraph.text != '代码':
            delete_paragraph(paragraph)

        else:
            delete_paragraph(paragraph)
            break
    # print('开头多余内容删除完成！')


    row_sum = len(doc.paragraphs)
    for row in range(0, row_sum, 1):
        # 删除每页标题
        if doc.paragraphs[row].text == '检查位置' and row + 8 < row_sum :
            row_start = row
            for row2 in range(row_start - 1, row + 8, 1):
                doc.paragraphs[row2].clear()


        # 删除页脚和下页页眉接口文字
        elif doc.paragraphs[row].text == 'Tianjin   ':
            row_start = row
            for row2 in range(row_start - 4, row + 9, 1):
                doc.paragraphs[row2].clear()


        # 清空最后的页脚
        elif len(doc.paragraphs[row].text) > 4:
            if doc.paragraphs[row].text == 'AUDIT' or doc.paragraphs[row].text[0:4] == '2020' or (doc.paragraphs[row].text[
                2] == '/'and doc.paragraphs[row].text[0].isdigit()):
                doc.paragraphs[row].clear()

        #清空独立的标题
        if doc.paragraphs[row].text[0:3].isdigit() and doc.paragraphs[row].text[0:4].isdigit()== False :
            if int(doc.paragraphs[row].text[0:3]) > 99 and int(doc.paragraphs[row].text[0:3]) < 1000:
                doc.paragraphs[row].clear()
        #清除问题代码
        if len(doc.paragraphs[row].text) > 3 and doc.paragraphs[row].text[0:4].isdigit():
            doc.paragraphs[row].clear()

        #清除抱怨类型的 代码和+权重+ABC
        if len(doc.paragraphs[row].text) > 0 and len(doc.paragraphs[row].text) < 4 and doc.paragraphs[row].text[0:1].isupper():
            doc.paragraphs[row].clear()

        #清除区代码
        if len(doc.paragraphs[row].text) < 2 and doc.paragraphs[row].text.isdigit():
            doc.paragraphs[row].clear()



    # print('页眉页脚删除完成！')

    # 删除空白行
    for paragraph in doc.paragraphs:
        if paragraph.text == '':
            delete_paragraph(paragraph)
    # doc.save('D:\\python\\docx_word_handle//全部处理后.docx')

    # doc.save('D:\\python\\docx_word_handle//测试.docx')


    # 进行 DataFrame构造，形成excel

    df = pd.DataFrame(columns=["问题编号","评价车型及日期","检查位置", '抱怨类型', '分值', '部门'])
    row_sum = len(doc.paragraphs)
    row_df = 1

    # 向DataFrame中录入数据
    for row in range(0, row_sum, 1):
        if row % 4 == 0 and row+3 < row_sum:
            a = {"问题编号": row_df, "评价车型及日期": file_name, "检查位置": doc.paragraphs[row].text,
                 '抱怨类型': doc.paragraphs[row + 1].text, '分值' \
                     : doc.paragraphs[row + 2].text, '部门': doc.paragraphs[row + 3].text}
            series = pd.Series(a)
            df = df.append(series,ignore_index=True)
            row_df = row_df + 1


    df.to_excel(excel_path,index=False)

if pdf_to_excel == "Y":
    print('批量Word转Excel开始')
    files = os.listdir(dirs) #获取文件名列表
    fenshu = 1
    for item in files:
        if os.path.isfile(os.path.join(dirs,item)):
            if item.find('.docx') != -1:
                word_full_path = os.path.join(dirs, item)
                excel_full_path = os.path.join(dirs, item[:-5] + '.xlsx')
                docx_to_excel(word_full_path, excel_full_path,item[:-8])
                # os.remove(word_full_path)#删除使用后的word文档
                print('完成第{0}份'.format(fenshu))
                fenshu += 1
    print('批量Word转Excle完成！')


#******************************形成合并Excel*******************************

def MergeExcel(filepath,outfile):
    result=pd.DataFrame()
    files = os.listdir(filepath)  # 获取文件名列表
    for item in files:
        if os.path.isfile(os.path.join(filepath, item)):
            if item.find('.xlsx') != -1 and item.find('2020') != -1:
                excel_full_path = os.path.join(filepath, item)
                data1 = pd.read_excel(excel_full_path)
                result = result.append(data1)

    #############写出数据xlsx格式#######################
    result.to_excel(os.path.join(outfile,"奥迪特清单合并-完整.xlsx"),index=False)
    print('Excel合并完成')


if excel_as_one == "Y" :
    print('Excel合并开始')
    MergeExcel(dirs,dirs)




# *******************************Excel筛选匹配问题*******************
if excel_for_match == 'Y':
    result = pd.read_excel(os.path.join(dirs,"奥迪特清单合并-完整.xlsx"))

    try:
        result_match = result[((result["部门"] == '焊装') | (result["部门"] == '总装')| (result["部门"] == '冲压')) & (result["抱怨类型"] == '零件匹配') \
                              & (result["分值"] > 10)]


        fehler_modul = pd.read_excel(os.path.join(dirs, '0_大众品牌奥迪扣分模块划分.xlsx'))

        check_position = result_match.loc[:, '检查位置']
        result_match = result_match.set_index('检查位置').join(fehler_modul.set_index('检查位置'))

        result_match.reset_index(inplace=True)
        result_match.loc[:, '序号'] = range(1, len(result_match) + 1, 1)
        result_match = result_match[['序号', '问题编号', '评价车型及日期', '检查位置', '抱怨类型', '分值', '部门', '模块']]

        result_match.to_excel(os.path.join(dirs, "奥迪特清单合并-总焊匹配.xlsx"), index=False)
        print('Excel匹配类筛选完成')

    except:
        print('''请确认'奥迪特清单合并-完整'，分值列是否有误！''')
        modul_audit_count = 'N'



#*******************************平均扣分计算****************************************

if modul_audit_count == 'Y':

    try:
        audit_match = pd.read_excel(os.path.join(dirs,"奥迪特清单合并-总焊匹配.xlsx"))

    except FileNotFoundError:
        print("请确认 奥迪特清单合并-总焊匹配是否已建立！")

    else:
        fahrzeug_number = len(audit_match['评价车型及日期'].unique())
        print('*****************各模块扣分情况统计如下****************')
        print('统计车数为:{0}'.format(fahrzeug_number))


        if audit_match['模块'].isnull().any():
            print('检查是否有问题尚未划分模块！')

        else:

            print('Audit C1个数统计如下：')

            modul_C1_count = audit_match[ audit_match["模块"] == '前端']
            c1_aver = int(modul_C1_count['分值'].sum(axis=0)/20)/(int(fahrzeug_number))
            print('前端C1个数为：{0}；单车C1个数为:{1}'.format(modul_C1_count['分值'].sum(axis=0)/20,round(c1_aver,1)))

            modul_C1_count = audit_match[ audit_match["模块"] == '后端']
            c1_aver = int(modul_C1_count['分值'].sum(axis=0)/20)/(int(fahrzeug_number))
            print('后端C1个数为：{0}；单车C1个数为:{1}'.format(modul_C1_count['分值'].sum(axis=0)/20,round(c1_aver,1)))

            modul_C1_count = audit_match[ audit_match["模块"] == '车门']
            c1_aver = int(modul_C1_count['分值'].sum(axis=0)/20)/(int(fahrzeug_number))
            print('车门C1个数为：{0}；单车C1个数为:{1}'.format(modul_C1_count['分值'].sum(axis=0)/20,round(c1_aver,1)))

            modul_C1_count = audit_match[ audit_match["模块"] == '内饰']
            c1_aver = int(modul_C1_count['分值'].sum(axis=0)/20)/(int(fahrzeug_number))
            print('内饰C1个数为：{0}；单车C1个数为:{1}'.format(modul_C1_count['分值'].sum(axis=0)/20,round(c1_aver,1)))